/**
 * @author 0283000115
 * @date 2018年3月5日-上午11:09:11
 */
package com.zte.claa.inficombo.csa.app.dao.mysql.jdbcdao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.druid.pool.DruidDataSource;
import com.zte.claa.inficombo.csa.app.model.redis.DevMeteData;

/**
 * @author 0283000115
 *
 */
public class AppDataDao extends BaseDao {
	// date format
	public SimpleDateFormat datetimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	// 日志管理器
	private Logger logger = LoggerFactory.getLogger(AppDataDao.class);

	public static final String TPL_PDB = "_PROJ_DB_NAME_";
	public static final String TPL_TAB = "_TABLE_NAME_";
	public static final String TPL_DEVPARA = "_DEVEUI_PARA_";
	public static final String TPL_MSGTPFIELD = "_MSG_TP_FIELD_";
	// 根据关键字段查询（主要用于趋势分析）
	public static final String TPL_KEYFIELD = "_MSG_KEY_FIELD_";
	
	// 1.普通查询SQL
	public static String QUERY_SQL = "SELECT t.*, d.devname FROM " +
									 TPL_PDB + "."+TPL_TAB+" t, combodb.t_combo_project_dev d "+
									 " WHERE t.deveui = d.deveui "+
									 "   AND t.collecttime >= ? "+
									 "   AND t.collecttime <= ? "+
									 " ORDER BY t.collecttime DESC "+
									 " LIMIT ? OFFSET ? ";
	// 2.按deveui列表查询.
	public static String QUERY_SQL_DEV = "SELECT t.*, d.devname FROM " +
										 TPL_PDB + "."+TPL_TAB+" t, combodb.t_combo_project_dev d "+
										 " WHERE t.deveui = d.deveui "+
										 "   AND t.collecttime >= ? "+
										 "   AND t.collecttime <= ? "+
										 "   AND t.deveui IN ("+TPL_DEVPARA+") "+
										 " ORDER BY t.collecttime DESC "+
										 " LIMIT ? OFFSET ? ";
	
	// 3.按最新时间查询每一个deveui对应的数据
	public static String QUERY_SQL_LATESTDATA = "SELECT t.*, d.devname FROM combodb.t_combo_project_dev d, " + 
										TPL_PDB + "."+TPL_TAB+" t " + " "+
										" JOIN (SELECT MAX(collecttime) AS maxtime, deveui FROM "+ " " +
										       TPL_PDB + "."+TPL_TAB + " "+
										       " WHERE collecttime >= ? " + 
										       " GROUP BY deveui) tt ON t.deveui = tt.deveui "+ 
										       " AND t.collecttime = tt.maxtime " +
										" WHERE t.deveui IN ("+TPL_DEVPARA+") "+
										"   AND t.deveui = d.deveui " + 
										" ORDER BY t.collecttime DESC ";
	
	// 4.按最新时间查询每一个deveui下不同msgtype对应的数据（主要用于油田设备的最新坐标查询）
	public static String QUERY_SQL_LATESTDATA_BYMSGTYPE = "SELECT t.*, d.devname FROM combodb.t_combo_project_dev d, "+ 
										TPL_PDB + "."+TPL_TAB+" t " + " " +
										" JOIN (SELECT MAX(collecttime) AS maxtime, deveui, "+TPL_MSGTPFIELD+" " + 
										       " FROM "+ TPL_PDB + "."+TPL_TAB + " "+
										       " WHERE collecttime >= ? " + 
										       " GROUP BY deveui, "+TPL_MSGTPFIELD+") tt ON t.deveui = tt.deveui "+ 
										       " AND t."+TPL_MSGTPFIELD+" = tt."+TPL_MSGTPFIELD+" " +
										       " AND t.collecttime = tt.maxtime " +
										" WHERE t.deveui IN ("+TPL_DEVPARA+") "+ 
										"   AND t.deveui = d.deveui " + 
										" ORDER BY t.collecttime DESC ";
	
	// 5.指定deveui和keyfield字段查询 （统计分析）
	public static String QUERY_SQL_DEV_KV = "SELECT t.*, d.devname FROM " +
										 TPL_PDB + "."+TPL_TAB+" t, combodb.t_combo_project_dev d "+
										 " WHERE t.deveui = d.deveui "+
										 "   AND t.collecttime >= ? "+
										 "   AND t.collecttime <= ? "+
										 "   AND t.deveui IN ("+TPL_DEVPARA+") "+
										 "   AND t."+TPL_KEYFIELD+" = ? " +
										 " ORDER BY t.collecttime DESC "+
										 " LIMIT ? OFFSET ? ";
	
	/**
	 * 构建器
	 * 
	 * @param dataSource
	 */
	public AppDataDao(DruidDataSource dataSource){
		super(dataSource);
	}
	
	/**
	 * 保存metedata
	 * 
	 * @param meteData
	 * @throws Exception
	 */
	public void saveDevMeteData(DevMeteData meteData, String tabName) throws Exception {
		Map<String, Object> dmap = meteData.getMdata();
		if(dmap == null || dmap.size() == 0){
			logger.warn("No mete data field to be saved to table: "+tabName);
			return;
		}
		String[] fnmArr = this.getFieldNames(meteData);
		String pdbName = this.getProjDBName(meteData.getProjectid());
		String sql = this.buildInsertSQL(fnmArr, tabName, pdbName);
		PreparedStatement statement = null;
		Connection dbConn = this.getDBConnection();		
		try {
			statement = dbConn.prepareStatement(sql);	
			// 准备数据.
			Timestamp rptTime = meteData.getRpttime();
			String devEui = meteData.getDeveui();
			statement.setTimestamp(1, rptTime);
			statement.setString(2, devEui);
			if(fnmArr != null){
				int len = fnmArr.length;
				for(int i=0; i<len; i++){
					String fnm = fnmArr[i];
					Object fva = dmap.get(fnm);
					fva = (fva == null) ? "" : fva;
					statement.setObject(i+3, fva);
				}
			}
			statement.execute();
			// autocommit mode...
			// dbConn.commit();
		} finally {
			this.releaseStatement(statement);
			this.releaseConnection(dbConn);			
		}		
	}

	/**
	 * 保存metedata
	 * 
	 * @param meteData
	 * @throws Exception
	 */
	public void saveAicDevMeteData(DevMeteData meteData, String tabName, String photoid) throws Exception {
		Map<String, Object> dmap = meteData.getMdata();
		if(dmap == null || dmap.size() == 0){
			logger.warn("No mete data field to be saved to table: "+tabName);
			return;
		}
		String[] fnmArr = this.getFieldNames(meteData);
		String pdbName = this.getProjDBName(meteData.getProjectid());	
		// 删除AIC业务数据表中相同photoid的记录
		int result = deleteAicDevMeteData(pdbName, tabName, photoid);
		logger.info("delete row by photoid, photoid:"+photoid+",rownum:"+result);
		/*
		if(1 == result){
			logger.info("delete row by photoid successfully, photoid:"+photoid);
		}else{
			logger.error("delete row by photoid fail, photoid:"+photoid);
			//return;
		}
		*/
		String sql = this.buildInsertSQL(fnmArr, tabName, pdbName);
		PreparedStatement statement = null;
		Connection dbConn = this.getDBConnection();		
		try {
			statement = dbConn.prepareStatement(sql);	
			// 准备数据.
			Timestamp rptTime = meteData.getRpttime();
			String devEui = meteData.getDeveui();
			statement.setTimestamp(1, rptTime);
			statement.setString(2, devEui);
			if(fnmArr != null){
				int len = fnmArr.length;
				for(int i=0; i<len; i++){
					String fnm = fnmArr[i];
					Object fva = dmap.get(fnm);
					fva = (fva == null) ? "" : fva;
					statement.setObject(i+3, fva);
				}
			}
			statement.execute();
			// autocommit mode...
			// dbConn.commit();
		} finally {
			this.releaseStatement(statement);
			this.releaseConnection(dbConn);			
		}		
	}	
	
	/**
	 * 保存metedata
	 * 
	 * @param meteData
	 * @throws Exception
	 */
	public void updateAicDevMeteData(DevMeteData meteData, String tabName, String photoid) throws Exception {
		Map<String, Object> dmap = meteData.getMdata();
		if(dmap == null || dmap.size() == 0){
			logger.warn("No mete data field to be saved to table: "+tabName);
			return;
		}
		dmap.remove("photoid");
		String[] fnmArr = this.getFieldNames(meteData);
		String pdbName = this.getProjDBName(meteData.getProjectid());	
		String sql = this.buildUpdateSQL(fnmArr, tabName, pdbName, photoid);
		PreparedStatement statement = null;
		Connection dbConn = this.getDBConnection();		
		try {
			statement = dbConn.prepareStatement(sql);	
			// 准备数据.
			Timestamp rptTime = meteData.getRpttime();
			String devEui = meteData.getDeveui();
			statement.setTimestamp(1, rptTime);
			statement.setString(2, devEui);
			if(fnmArr != null){
				int len = fnmArr.length;
				for(int i=0; i<len; i++){
					String fnm = fnmArr[i];
					Object fva = dmap.get(fnm);
					fva = (fva == null) ? "" : fva;
					statement.setObject(i+3, fva);
				}
			}
			statement.execute();
			// autocommit mode...
			// dbConn.commit();
		} finally {
			this.releaseStatement(statement);
			this.releaseConnection(dbConn);			
		}		
	}		
	
	private int deleteAicDevMeteData(String pdbName, String tabName, String photoid) throws Exception{
		StringBuilder sb = new StringBuilder();
		sb.append("delete from").append(" ").append(pdbName).append(".").append(tabName).append(" ").append("where photoid=?");		
		String sql = sb.toString();
		PreparedStatement statement = null;
		Connection dbConn = this.getDBConnection();	
		int result = 1;
		try {
			statement = dbConn.prepareStatement(sql);	
			// 准备数据.
			statement.setString(1, photoid);
			result = statement.executeUpdate();			
			// autocommit mode...
			// dbConn.commit();
		} finally {
			this.releaseStatement(statement);
			this.releaseConnection(dbConn);			
		}
		return result;
	}
	
	/**
	 * 批量保存metedata，要求相同类型相同字段；
	 * 
	 * @param meteData
	 * @throws Exception
	 */
	public void saveDevMeteDataByBatch(List<DevMeteData> meteDatas, String tabName) throws Exception {
		if(meteDatas == null || meteDatas.size() == 0){
			logger.warn("There is no data row to be saved: "+tabName);
			return;
		}
		DevMeteData meteData1 = meteDatas.get(0);
		String[] fnmArr = this.getFieldNames(meteData1);
		String pdbName = this.getProjDBName(meteData1.getProjectid());
		String sql = this.buildInsertSQL(fnmArr, tabName, pdbName);
		PreparedStatement statement = null;
		Connection dbConn = this.getDBConnection();		
		try {
			statement = dbConn.prepareStatement(sql);	
			int batchNum = 0;
			for(DevMeteData meteData : meteDatas){
				if(meteData == null){
					continue;
				}
				Map<String, Object> dmap = meteData.getMdata();
				if(dmap == null || dmap.size() == 0){
					continue;
				}
				// 准备数据.
				Timestamp rptTime = meteData.getRpttime();
				String devEui = meteData.getDeveui();
				statement.setTimestamp(1, rptTime);
				statement.setString(2, devEui);
				if(fnmArr != null){
					int len = fnmArr.length;
					for(int i=0; i<len; i++){
						String fnm = fnmArr[i];
						Object fva = dmap.get(fnm);
						// fva = (fva == null) ? "0" : fva; 
						statement.setObject(i+3, fva);
					}
				}				
				statement.addBatch();
				batchNum++;
				// 最大50一批.
				if (batchNum >= 50) {
					statement.executeBatch();					
					logger.info("Save data to table: "+tabName+", batchnum="+batchNum);
					batchNum = 0;
				}
			}
			if (batchNum > 0) {
				statement.executeBatch();
				logger.info("Save data to table: "+tabName+", batchnum="+batchNum);
			}
			// autocommit mode...
			// dbConn.commit();
		} finally {
			this.releaseStatement(statement);
			this.releaseConnection(dbConn);			
		}
	}
	
	/**
	 * 构造入库脚本。
	 * 
	 * @param meteData
	 * @param tabName
	 * @return
	 */
	private String buildInsertSQL(String[] fnmArr, String tabName, String pdbName){		
		StringBuilder sb = new StringBuilder();
		sb.append("insert into").append(" ").append(pdbName).append(".").append(tabName).append("(collecttime, deveui");
		StringBuilder sb2 = new StringBuilder();
		sb2.append(" ").append("values(?,?");
		if(fnmArr != null){
			int len = fnmArr.length;
			for(int i=0; i<len; i++){
				sb.append(", ").append(fnmArr[i]);
				sb2.append(", ").append("?");
			}
		}
		sb.append(")");
		sb2.append(")");
		sb.append(sb2.toString());
		return sb.toString();
	}
	
	/**
	 * 构造更新AIC库脚本。
	 * 
	 * @param meteData
	 * @param tabName
	 * @return
	 */
	private String buildUpdateSQL(String[] fnmArr, String tabName, String pdbName, String photoid){		
		StringBuilder sb = new StringBuilder();
		sb.append("update ").append(pdbName).append(".").append(tabName).append(" set ");
		sb.append("collecttime=?,").append("deveui=?,");
		if(fnmArr != null){
			int len = fnmArr.length;
			boolean firstfldflag = true;
			for(int i=0; i<len; i++){
				if(fnmArr[i].equals("photoid")){
					continue;
				}
				if(true == firstfldflag){
					sb.append(fnmArr[i]).append("=").append("?");
					firstfldflag = false;
				}else{
					sb.append(", ").append(fnmArr[i]).append("=").append("?");
				}
					
				firstfldflag = false;
			}
		}
		sb.append(" where ").append("photoid=").append(photoid);
		return sb.toString();
	}
	
	/**
	 * 构造入库脚本。
	 * 
	 * @param meteData
	 * @param tabName
	 * @return
	 */
	private String[] getFieldNames(DevMeteData meteData){		
		List<String> fnmList = new ArrayList<String>();
		Map<String, Object> fmap = meteData.getMdata();
		if(fmap != null){
			Iterator<String> it = fmap.keySet().iterator();
			while(it.hasNext()){
				String fnm = it.next();
				fnmList.add(fnm);
			}
		}
		return (String[])fnmList.toArray(new String[fnmList.size()]);
	}
	
	// --------------------- 数据查询 -----------------------------// 
	/**
	 * 查询metedata
	 * 
	 * @param meteData
	 * @throws Exception
	 * 		private Timestamp beginTime;
			private Timestamp endTime;
			private List<String> devEuis = null;
			private long pageIndex = 0;
			private long pageSize = 5000;	
	 */
	public List<String> queryMeteData(AppDataQueryPara para, String tabName) throws Exception {
		// 1.准备SQL.
		String qrySql = this.prepareSql(para, tabName);
		logger.info("query sql: "+qrySql);
		// 2.执行查询.
		Timestamp bt = para.getBeginTime();
		if(bt == null){
			bt = new Timestamp(System.currentTimeMillis() - 365*24*3600*1000L);
		}
		Timestamp et = para.getEndTime();
		if(et == null){
			et = new Timestamp(System.currentTimeMillis() + 24*3600*1000L);
		}		
		PreparedStatement statement = null;
		ResultSet ret = null;
		Connection dbConn = this.getDBConnection();		
		try {
			statement = dbConn.prepareStatement(qrySql);	
			// 准备参数
			statement.setTimestamp(1, bt);
			statement.setTimestamp(2, et);
			long offset = 1L*(para.getPageIndex() - 1)*para.getBatch();
			statement.setLong(3, para.getBatch());
			statement.setLong(4, offset);
			ret = statement.executeQuery();
			return this.parseQueryRet(ret);			
		} finally {
			this.releaseResultset(ret);
			this.releaseStatement(statement);
			this.releaseConnection(dbConn);			
		}		
	}
	
	/**
	 * 查询metedata
	 * 
	 * @param meteData
	 * @throws Exception
	 * 		private Timestamp beginTime;
			private Timestamp endTime;
			private List<String> devEuis = null;
			private long pageIndex = 0;
			private long pageSize = 5000;	
	 */
	public List<Map<String, Object>> queryMeteDataAsMap(AppDataQueryPara para, String tabName) throws Exception {
		// 1.准备SQL.
		String qrySql = this.prepareSql(para, tabName);
		logger.info("query sql: "+qrySql);
		// 2.执行查询.
		Timestamp bt = para.getBeginTime();
		if(bt == null){
			bt = new Timestamp(System.currentTimeMillis() - 365*24*3600*1000L);
		}
		Timestamp et = para.getEndTime();
		if(et == null){
			et = new Timestamp(System.currentTimeMillis() + 24*3600*1000L);
		}		
		PreparedStatement statement = null;
		ResultSet ret = null;
		Connection dbConn = this.getDBConnection();		
		try {
			statement = dbConn.prepareStatement(qrySql);	
			// 准备参数
			statement.setTimestamp(1, bt);
			statement.setTimestamp(2, et);
			long offset = 1L*(para.getPageIndex() - 1)*para.getBatch();  //Unintentional integer overflow
			statement.setLong(3, para.getBatch());
			statement.setLong(4, offset);
			ret = statement.executeQuery();
			return this.parseQueryRetAsMap(ret);			
		} finally {
			this.releaseResultset(ret);
			this.releaseStatement(statement);
			this.releaseConnection(dbConn);			
		}		
	}
	
	/**
	 * 按collecttime分deveui查询最近上报的数据metedata
	 * 
	 * @param meteData
	 * @throws Exception
	 * 		private Timestamp beginTime;
			private Timestamp endTime;
			private List<String> devEuis = null;
			private long pageIndex = 0;
			private long pageSize = 5000;	
	 */
	public List<Map<String, Object>> queryDevLatestMeteDataAsMap(AppDataQueryPara para, String tabName) throws Exception {
		// 1.准备SQL.
		String qrySql = this.prepareSql4LatestQry(para, tabName);
		logger.info("queryDevLatestMeteDataAsMap sql: "+qrySql);
		// 2.执行查询.
		PreparedStatement statement = null;
		ResultSet ret = null;
		Connection dbConn = this.getDBConnection();		
		try {
			statement = dbConn.prepareStatement(qrySql);	
			// 如果不设置，最大查询3天内的数据》查询效率考虑。
			Timestamp bt = para.getBeginTime();
			if(bt == null){
				bt = new Timestamp(System.currentTimeMillis() - 3*24*3600*1000L);
			}	
			statement.setTimestamp(1, bt);	
			ret = statement.executeQuery();
			return this.parseQueryRetAsMap(ret);			
		} finally {
			this.releaseResultset(ret);
			this.releaseStatement(statement);
			this.releaseConnection(dbConn);			
		}		
	}
	
	/**
	 * 按collecttime分deveui和关键字段查询最近上报的数据metedata
	 * 
	 * @param meteData
	 * @throws Exception
	 * 		private Timestamp beginTime;
			private Timestamp endTime;
			private List<String> devEuis = null;
			private long pageIndex = 0;
			private long pageSize = 5000;	
	 */
	public List<Map<String, Object>> queryDevLatestMeteDataAsMapByKeyField(AppDataQueryPara para, String tabName, String keyFieldName) throws Exception {
		// 1.准备SQL.
		String qrySql = this.prepareSql4LatestQryWithKeyField(para, tabName, keyFieldName);
		logger.info("queryDevLatestMeteDataAsMapByKeyField sql: "+qrySql);
		// 2.执行查询.
		PreparedStatement statement = null;
		ResultSet ret = null;
		Connection dbConn = this.getDBConnection();		
		try {
			statement = dbConn.prepareStatement(qrySql);	
			// 最大查询3天内的数据》查询效率考虑。
			Timestamp bt = para.getBeginTime();
			if(bt == null){
				bt = new Timestamp(System.currentTimeMillis() - 3*24*3600*1000L);
			}
			statement.setTimestamp(1, bt);	
			ret = statement.executeQuery();
			return this.parseQueryRetAsMap(ret);			
		} finally {
			this.releaseResultset(ret);
			this.releaseStatement(statement);
			this.releaseConnection(dbConn);			
		}		
	}
	
	/**
	 * 根据deveui和keyfield列查询metedata
	 * 
	 * @param meteData
	 * @throws Exception			
	 */
	public List<Map<String, Object>> queryMeteDataByDevKeyField(AppDataQueryPara para, String keyFieldValue, String tabName) throws Exception {
		// 1.准备SQL.
		String qrySql = this.prepareSqlWithKeyField(para, tabName);
		logger.info("queryMeteDataAsMapByKeyField sql: "+qrySql);
		// 2.执行查询.
		Timestamp bt = para.getBeginTime();
		if(bt == null){
			bt = new Timestamp(System.currentTimeMillis() - 365*24*3600*1000L);
		}
		Timestamp et = para.getEndTime();
		if(et == null){
			et = new Timestamp(System.currentTimeMillis() + 24*3600*1000L);
		}		
		PreparedStatement statement = null;
		ResultSet ret = null;
		Connection dbConn = this.getDBConnection();		
		try {
			statement = dbConn.prepareStatement(qrySql);	
			// 准备参数
			statement.setTimestamp(1, bt);
			statement.setTimestamp(2, et);
			statement.setString(3, keyFieldValue);
			long offset = 1L*(para.getPageIndex() - 1)*para.getBatch(); //Unintentional integer overflow
			statement.setLong(4, para.getBatch());
			statement.setLong(5, offset);
			ret = statement.executeQuery();
			return this.parseQueryRetAsMap(ret);			
		} finally {
			this.releaseResultset(ret);
			this.releaseStatement(statement);
			this.releaseConnection(dbConn);			
		}		
	}
	
	/**
	 * 准备SQL
	 * 
	 * @param para
	 * @param tabName
	 * @return
	 */
	protected String prepareSql(AppDataQueryPara para, String tabName){
		String projectId = para.getProjectId();
		String pdbName = this.getProjDBName(projectId);
		List<String> devEuis = para.getDevEUIs();
		String qrySql = "";
		if(devEuis == null || devEuis.size() == 0){
			qrySql = QUERY_SQL.replaceAll(TPL_PDB, pdbName);
			qrySql = qrySql.replaceAll(TPL_TAB, tabName);
		} else {
			qrySql = QUERY_SQL_DEV.replaceAll(TPL_PDB, pdbName);
			qrySql = qrySql.replaceAll(TPL_TAB, tabName);
			StringBuffer sb = new StringBuffer();
			for(int i=0; i<devEuis.size(); i++){
				if(i > 0){
					sb.append(",");
				}
				sb.append("'").append(devEuis.get(i)).append("'");
			}
			qrySql = qrySql.replaceAll(TPL_DEVPARA, sb.toString());
		}
		return qrySql;
	}
	
	/**
	 * 准备SQL
	 * 
	 * @param para
	 * @param tabName
	 * @return
	 */
	protected String prepareSql4LatestQry(AppDataQueryPara para, String tabName) {
		String projectId = para.getProjectId();
		String pdbName = this.getProjDBName(projectId);
		List<String> devEuis = para.getDevEUIs();
		String qrySql = QUERY_SQL_LATESTDATA.replaceAll(TPL_PDB, pdbName);
		qrySql = qrySql.replaceAll(TPL_TAB, tabName);
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < devEuis.size(); i++) {
			if (i > 0) {
				sb.append(",");
			}
			sb.append("'").append(devEuis.get(i)).append("'");
		}
		return qrySql.replaceAll(TPL_DEVPARA, sb.toString());
	}
	
	/**
	 * 准备SQL
	 * 
	 * @param para
	 * @param tabName
	 * @return
	 */
	protected String prepareSql4LatestQryWithKeyField(AppDataQueryPara para, String tabName, String keyFieldName) {
		String projectId = para.getProjectId();
		String pdbName = this.getProjDBName(projectId);
		List<String> devEuis = para.getDevEUIs();
		String qrySql = QUERY_SQL_LATESTDATA_BYMSGTYPE.replaceAll(TPL_PDB, pdbName);
		qrySql = qrySql.replaceAll(TPL_TAB, tabName);
		qrySql = qrySql.replaceAll(TPL_MSGTPFIELD, keyFieldName);
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < devEuis.size(); i++) {
			if (i > 0) {
				sb.append(",");
			}
			sb.append("'").append(devEuis.get(i)).append("'");
		}
		return qrySql.replaceAll(TPL_DEVPARA, sb.toString());
	}
	
	/**
	 * 准备按deveui和keyfield的查询SQL 
	 * 
	 * @param para
	 * @param tabName
	 * @return
	 */
	protected String prepareSqlWithKeyField(AppDataQueryPara para, String tabName){
		String projectId = para.getProjectId();
		String pdbName = this.getProjDBName(projectId);
		List<String> devEuis = para.getDevEUIs();
		String qrySql = QUERY_SQL_DEV_KV.replaceAll(TPL_PDB, pdbName);
		qrySql = qrySql.replaceAll(TPL_TAB, tabName);
		qrySql = qrySql.replaceAll(TPL_KEYFIELD, para.getKeyFieldName());
		StringBuffer sb = new StringBuffer();
		for(int i=0; i<devEuis.size(); i++){
			if(i > 0){
				sb.append(",");
			}
			sb.append("'").append(devEuis.get(i)).append("'");
		}
		return qrySql.replaceAll(TPL_DEVPARA, sb.toString());
	}
	
	/**
	 * 解析查询结果： 返回json字符串列表
	 * 
	 * @param ret
	 * @return
	 * @throws Exception
	 */
	protected List<String> parseQueryRet(ResultSet result) throws Exception {
		List<String> ret = new ArrayList<String>();
		ResultSetMetaData mdatas = result.getMetaData();
		int colNum = mdatas.getColumnCount();
		List<String> colNames = new ArrayList<String>();
		for(int i=1; i<= colNum; i++){
			colNames.add(mdatas.getColumnLabel(i));
		}
		while(result.next()){
			JSONObject json = new JSONObject();
			for(String colLabel : colNames){
				Object vo = result.getObject(colLabel);
				if(Timestamp.class.isInstance(vo)){
					json.put(colLabel, datetimeFormat.format(vo));
				} else {
					json.put(colLabel, vo);
				} 
			}
			ret.add(json.toString());
		}
		return ret;
	}
	
	/**
	 * 解析查询结果： 按map返回.
	 * 
	 * @param ret
	 * @return
	 * @throws Exception
	 */
	protected List<Map<String, Object>> parseQueryRetAsMap(ResultSet result) throws Exception {
		List<Map<String, Object>> ret = new ArrayList<Map<String, Object>>();
		ResultSetMetaData mdatas = result.getMetaData();
		int colNum = mdatas.getColumnCount();
		List<String> colNames = new ArrayList<String>();
		for(int i=1; i<= colNum; i++){
			colNames.add(mdatas.getColumnLabel(i));
		}
		while(result.next()){
			Map<String, Object> map = new HashMap<String, Object>();
			for(String colLabel : colNames){
				Object vo = result.getObject(colLabel);
				if(Timestamp.class.isInstance(vo)){
					map.put(colLabel, datetimeFormat.format(vo));
				} else {
					map.put(colLabel, vo);
				}
			}
			ret.add(map);
		}
		return ret;
	}

}
